<?php
/**
 * 数组生成Excel
 * @author zouhao  zouhao619@gmail.com
 * 使用示例
 * $excel =new Excel();
	$data=array(
			array('id'=>1,'name'=>'天命1'),
			array('id'=>2,'name'=>'天命2')
		);
	$header=array('ID','角色名');
	$excel->setFileName('aaa');
	$excel->setTitle('工作区1');
	$excel->create($data,$header);
	
	读取excel
	$data=$excel->excelToArray ('1.xls' );
 */
class Excel {
	private $excelObj;
	private $fileName='download.xls';
	/**
	 * 设置下载时文件名
	 * @param string $fileName
	 */
	public function setFileName($fileName){
		$this->fileName=$fileName.'.xls';
	}
	/**
	 * 设置标题
	 * @param string $title        	
	 */
	public function setTitle($title) {
		$this->excelObj->getActiveSheet ()->setTitle ( $title );
	}
	public function __construct() {
		// 先取消原框架的自动注册机制,避免和excel自动加载机制冲突
		spl_autoload_unregister ( 'autoload' );
		require LIBRARY_PATH . '/PHPExcel/PHPExcel.php';
		$this->excelObj = new PHPExcel ();
	}
	/**
	 * 读取Excel
	 * @param string $filePath
	 * @return boolean
	 */
	public function excelToArray($filePath){
		$PHPReader = new PHPExcel_Reader_Excel2007();
		if(!$PHPReader->canRead($filePath)){
			$PHPReader = new PHPExcel_Reader_Excel5();
			if(!$PHPReader->canRead($filePath)){
				return false;
			}
		}
		$PHPExcel = $PHPReader->load($filePath);
		/**读取excel文件中的第一个工作表*/
		$currentSheet = $PHPExcel->getSheet(0);
		/**取得最大的列号*/ 
		$allColumn = $currentSheet->getHighestColumn(); 
		/**取得一共有多少行*/ 
		$allRow = $currentSheet->getHighestRow(); 
		$i=0;
		for($currentRow = 2;$currentRow <= $allRow;$currentRow++){
			$j=0;
			/**从第A列开始输出*/
			for($currentColumn= 'A';$currentColumn<= $allColumn; $currentColumn++){
				$val = (string)$currentSheet->getCellByColumnAndRow(ord($currentColumn) - 65,$currentRow)->getValue();/**ord()将字符转为十进制数*/
				if($currentColumn == 'A')
				{
					$data[$i][$j]=$val;
				}else{
					//echo $val;
					/**如果输出汉字有乱码，则需将输出内容用iconv函数进行编码转换，如下将gb2312编码转为utf-8编码输出*/
					$data[$i][$j]=iconv('utf-8','gb2312', $val);
				}
				++$j;
			}
			++$i;
		}
		return $data;
	}
	/**
	 * 生成Excel表格
	 * @param array $data		二维数组
	 * @param array $replace	需要替换的数组
	 */
	public function create($data,$header=array(),$replace = null) {
		empty($data) and exit('没有数据');
		$keys = $this->_getCharByNumber ( $data );
		$this->_createHeader ( $header, $keys );
		$header_key=key($header);//用做判断$header是否有key
		$j=0;
		foreach ( $data as $i=>$vo ) {
			$j=0;
			if($header_key===0){
				foreach ( $vo as $key => $item ) {
					if (isset ( $replace [$key] )){
						$this->excelObj->setActiveSheetIndex ( 0 )->setCellValue ( $keys [$j] . ($i + 2), $replace [$key] [$item] );
					}else{
						$this->excelObj->setActiveSheetIndex ( 0 )->setCellValue ( $keys [$j] . ($i + 2), $item );
					}
					++$j;
				}
			}else{
				foreach ( $header as $key => $item ) {
					if (isset ( $replace [$key] )){
						if(isset($vo[$key])){
							$this->excelObj->setActiveSheetIndex ( 0 )->setCellValueExplicit ( $keys [$j] . ($i + 2), $replace [$key] [$vo[$key]],PHPExcel_Cell_DataType::TYPE_STRING );
						}else{
							$this->excelObj->setActiveSheetIndex ( 0 )->setCellValueExplicit ( $keys [$j] . ($i + 2), $replace [$key] [$vo],PHPExcel_Cell_DataType::TYPE_STRING );	}
					}else if(isset($vo[$key])){
						$this->excelObj->setActiveSheetIndex ( 0 )->setCellValueExplicit ( $keys [$j] . ($i + 2), $vo[$key],PHPExcel_Cell_DataType::TYPE_STRING );
					}else{
						$this->excelObj->setActiveSheetIndex ( 0 )->setCellValueExplicit ( $keys [$j] . ($i + 2), $item,PHPExcel_Cell_DataType::TYPE_STRING );
					}
					++$j;
				}
			}
		}
		//输出到临时缓冲区  提供下载
		header ( "Content-Type: application/force-download" );
		header ( "Content-Type: application/octet-stream" );
		header ( "Content-Type: application/download" );
		header ( 'Content-Disposition:inline;filename="'.$this->fileName.'"' );
		header ( "Content-Transfer-Encoding: binary" );
		header ( "Cache-Control: must-revalidate, post-check=0, pre-check=0" );
		header ( "Pragma: no-cache" );
		$objWriter = PHPExcel_IOFactory::createWriter ( $this->excelObj, 'Excel5' );
		$objWriter->save ( 'php://output' );
	}
	/**
	 * 根据总数,返回列数组
	 * @param int $count
	 * @return array
	 */
	private function _getCharByNumber($data) {
		// 自动减去头部
		$count = count ( $data ['0'] );
		$keys=array();
		for($number = 1; $number <=$count; $number ++) {
			$divisor= intval($number / 26);
			$char = chr ( 64 + $number % 26 );
			$char = $divisor == 0 ? $char : chr ( 64 +$divisor) . $char;
			$keys [] = $char;
		}
		return $keys;
	}
	/**
	 * 创建头部
	 * @param array $data        	
	 */
	private function _createHeader($header, $keys) {
		$header = array_combine ( $keys, $header );
		foreach ( $header as $key => $vo ) {
			$this->excelObj->setActiveSheetIndex ( 0 )->setCellValue ( "{$key}1", $vo );
		}
	}
}